package com.ruoyi.project.easyexcel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.*;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.alibaba.excel.write.style.column.SimpleColumnWidthStyleStrategy;
import com.github.pagehelper.PageHelper;
import com.ruoyi.project.easyexcel.entity.PsinfoAutomonirotDataAnomalSignExamine;
import com.ruoyi.project.system.service.ISysNoticeService;
import com.ruoyi.project.system.service.ISysUserService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileItemFactory;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.poi.ss.usermodel.Cell;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.commons.CommonsMultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @Author huyunqiang
 * @Description 导出excel 合并复杂表头
 * @Date 2022/11/11 11:12
 **/
@Slf4j
@Service
public class ExportExcelHeader {
    @Autowired
    ISysUserService iSysUserService;
    @Autowired
    ISysNoticeService iSysNoticeService;

    public static void main(String[] args) {
        //组装数据
        List<Users> dtoList = getData();
        //组装复杂头
        List<List<String>> head = getHead();
        List<Object> writeList = new ArrayList<>();
        //要合并的行，key：起始行，value：结束行
        Map<Integer, Integer> mapMerge = new HashMap<>();
        //序号
        int count = 1;
        //要合并的起始行 从0开始 = head的行数
        int index = 3;

        Collection<List<Users>> values = dtoList.stream().collect(Collectors.groupingBy(Users::getHzmc)).values();
//        for (List<Users> dtos : values) {
//            mapMerge.put(index,index+dtos.size()-1);
//            for (Users dto : dtos) {
//                List<Object> list = new ArrayList<>(7);
//                Collections.addAll(list, count, dto.getHzmc(), dto.getCyxm(), dto.getCyxb(), dto.getCyzjhm(), dto.getYhzgx(), dto.getLxdh());
//                writeList.add(list);
//            }
//            count++;
//            index += dtos.size();
//        }
        mapMerge.put(index,index+dtoList.size()-1);
        for (Users dto : dtoList) {
            List<Object> list = new ArrayList<>(7);
                Collections.addAll(list, count, dto.getHzmc(), dto.getCyxm(), dto.getCyxb(), dto.getCyzjhm(), dto.getYhzgx(), dto.getLxdh());
                writeList.add(list);
            count++;
            index += dtoList.size();
        }
        String fileName = "D:\\";
        //自定义合并策略 MyMergeStrategy(合并的起始行，要合并的列)
//        MyMergeStrategy myMergeStrategy = new MyMergeStrategy(mapMerge,Arrays.asList(0,1)); //有默认值 可以不配置
        EasyExcel.write(fileName + "test1.xlsx").head(head).sheet().doWrite(writeList);



    }

    public static void exportJobLogExcel(HttpServletResponse response) throws IOException {
        //组装数据
        List<Users> dtoList = getData();
        //组装复杂头
        List<List<String>> head = getHead();
        List<Object> writeList = new ArrayList<>();
        //要合并的行，key：起始行，value：结束行
        Map<Integer, Integer> mapMerge = new HashMap<>();
        //序号
        int count = 1;
        //要合并的起始行 从0开始 = head的行数
        int index = 3;

        mapMerge.put(index,index+dtoList.size()-1);
        for (Users dto : dtoList) {
            List<Object> list = new ArrayList<>(7);
            Collections.addAll(list, dto.getHzmc(), dto.getCyxm(), dto.getCyxb(), dto.getCyzjhm(), dto.getYhzgx(), dto.getLxdh());
            writeList.add(list);
            count++;
            index += dtoList.size();
        }
        String fileName = "D:\\";

        //设置第几列开始合并
        int[] mergeColumnIndex = {0,1,2,3,4,5,6,7,8};
        //设置第几行开始合并
        int mergeRowIndex = 2;

//        EasyExcelTitleHandler excelFillCellMergeStrategyUtils = new EasyExcelTitleHandler(mergeRowIndex, mergeColumnIndex);
        EasyExcelRowsTitleHandler test = new EasyExcelRowsTitleHandler();

        //自定义合并策略 MyMergeStrategy(合并的起始行，要合并的列)
//        MyMergeStrategy myMergeStrategy = new MyMergeStrategy(mapMerge,Arrays.asList(0,1)); //有默认值 可以不配置

        //本地导出
//        EasyExcel.write(fileName + "test1.xlsx").head(head).registerWriteHandler(excelFillCellMergeStrategyUtils).sheet().doWrite(writeList);
        EasyExcel.write(fileName + "test1.xlsx").head(head).registerWriteHandler(test)
                .sheet()
                //自适应列宽
                .registerWriteHandler(new SimpleColumnWidthStyleStrategy(40))
//                .registerWriteHandler(new XCellStyle())
                .doWrite(writeList);

        //浏览器导出
//       ExcelUtils.write(response,"ceshi.xlsx","用户",head,writeList);

        //导出到其他服务器《《《《《《《《《《《《《《《《《《《《《start<<<<<<<<<<<<<<<<<<<<
/*        String fileNam = new String("文件名称.xlsx".getBytes(), "UTF-8");
        response.addHeader("Content-Disposition", "filename=" + fileNam);
        //设置类型
        response.setContentType("application/octet-stream");
        //XWPFDocument转FileItem
        FileItemFactory factory = new DiskFileItemFactory(16, null);
        FileItem fileItem = factory.createItem("textField", "text/plain", true, fileNam);
        try {
            OutputStream os = fileItem.getOutputStream();
            EasyExcel.write(os).head(head).excelType(ExcelTypeEnum.XLSX)
                    .autoCloseStream(false) // 不要自动关闭，交给 Servlet 自己处理
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) // 基于 column 长度，自动适配。最大 255 宽度
                    .sheet("ceshi").doWrite(writeList);

            os.close();
            //FileItem转MultipartFile
            MultipartFile multipartFile = new CommonsMultipartFile(fileItem);
            //上传到文件服务器
            upload(multipartFile,"D:\\","测试.xlsx");
//            return minIOService.uploadFile(minIOService.getMinioClient(),multipartFile);
        } catch (Exception e) {
            e.printStackTrace();
        }*/

    }

    public static boolean upload(MultipartFile file, String path, String fileName) {
        String realPath = path + "\\" + fileName;
        File dest = new File(realPath);
        //判断文件父目录是否存在
        if (!dest.getParentFile().exists()) {
            dest.getParentFile().mkdir();
        }
        try {
            //保存文件
            file.transferTo(dest);
            return true;
        } catch (IllegalStateException | IOException e) {
            log.error("文件上传{} 异常", e.getMessage(),e);
            e.printStackTrace();
            return false;
        }

    }

    private static List<List<String>> getHead(){
        String firstRow = "常规监测因子";
        String familyMember = "家庭成员情况";
        List<String> first = Arrays.asList(firstRow, "序号", "序号");
        List<String> second = Arrays.asList(firstRow, "户主", "户主");
        List<String> third = Arrays.asList(firstRow,  familyMember, "成员姓名");
        List<String> fourth = Arrays.asList(firstRow,  familyMember, "成员性别");
        List<String> fifth = Arrays.asList(firstRow, familyMember, "证件号码");
        List<String> sixth = Arrays.asList(firstRow,  familyMember, "家庭关系");
        List<String> seventh = Arrays.asList(firstRow,  familyMember, "联系电话");
        return Arrays.asList(first, second, third, fourth, fifth, sixth, seventh);
    }


    private static List<Users> getData() {
        List<Users> list = new ArrayList<>();
        Users dto = new Users();
        dto.setHzmc("h:张三");
        dto.setCyxm("张三");
        dto.setCyxb("男");
        dto.setCyzjhm("123123123123123");
        dto.setYhzgx("本人");
        dto.setLxdh("123123123123");
        Users dto2 = new Users();
        dto2.setHzmc("h:张三");
        dto2.setCyxb("男");
        dto2.setCyxm("张小小");
        dto2.setCyzjhm("234234234234234-234");
        dto2.setYhzgx("父子");
        dto2.setLxdh("123123123123");
        Users dto3 = new Users();
        dto3.setHzmc("G:张三");
        dto3.setCyxb("女");
        dto3.setCyxm("张小花");
        dto3.setCyzjhm("22343342234234234");
        dto3.setYhzgx("父女");
        dto3.setLxdh("123123123123");
        Users dto4 = new Users();
        dto4.setHzmc("G:李小小");
        dto4.setCyxb("男");
        dto4.setCyxm("G:李小小");
        dto4.setCyzjhm("123123123123123");
        dto4.setYhzgx("本人");
        dto4.setLxdh("123123123123");
        Users dto5 = new Users();
        dto5.setHzmc("G:李小小");
        dto5.setCyxb("女");
        dto5.setCyxm("G韩小小");
        dto5.setCyzjhm("234234234234234");
        dto5.setYhzgx("夫妻");
        dto5.setLxdh("123123123123");
        Collections.addAll(list, dto, dto2, dto3, dto4,dto5);
        try {
            Thread.sleep(3000);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        for(int i=0;i<5000;i++){
            Users dto7 = new Users();
            dto7.setHzmc("G:李小小"+i);
            dto7.setCyxb("女"+i);
            dto7.setCyxm("G韩小小"+i);
            dto7.setCyzjhm("234234234234234"+i);
            dto7.setYhzgx("夫妻");
            dto7.setLxdh("12312312312"+i);
            list.add(dto7);
        }
        return list;
    }

    /**
     * 超大数据导出demo
     * @param response
     * @throws IOException
     */
    public void exportExcelMax(HttpServletResponse response) throws IOException {
        {
            OutputStream outputStream = null;
            try {
                long startTime = System.currentTimeMillis();
                System.out.println("导出开始时间:" + startTime);

                outputStream = response.getOutputStream();
                ExcelWriter writer = new ExcelWriter(outputStream, ExcelTypeEnum.XLSX);
                String fileName = new String(("excel100w").getBytes(), "UTF-8");

                //title
                Table table = new Table(1);
                String firstRow = "常规监测因子";
                String familyMember = "家庭成员情况";
                List<List<String>> titles = new ArrayList<List<String>>();
                titles.add(Arrays.asList(firstRow, "序号", "序号"));
                titles.add(Arrays.asList(firstRow, "户主", "户主"));
                titles.add(Arrays.asList(firstRow,  familyMember, "成员姓名"));
                titles.add(Arrays.asList(firstRow,  familyMember, "成员性别"));
                titles.add(Arrays.asList(firstRow, familyMember, "证件号码"));
                titles.add(Arrays.asList(firstRow, familyMember, "证件号码"));
                table.setHead(titles);

                //模拟统计查询的数据数量这里模拟100w
                int count = 196084;
                //记录总数:实际中需要根据查询条件进行统计即可
//                Integer totalCount = actResultLogMapper.findActResultLogByCondations(count);
                Integer totalCount = 196084;
                //每一个Sheet存放100w条数据
                Integer sheetDataRows = 50000;
                //每次写入的数据量20w
                Integer writeDataRows = 20000;
                //计算需要的Sheet数量
                Integer sheetNum = totalCount % sheetDataRows == 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows + 1);
                //计算一般情况下每一个Sheet需要写入的次数(一般情况不包含最后一个sheet,因为最后一个sheet不确定会写入多少条数据)
                Integer oneSheetWriteCount = sheetDataRows / writeDataRows;
                //计算最后一个sheet需要写入的次数
                Integer lastSheetWriteCount = totalCount % sheetDataRows == 0 ? oneSheetWriteCount : (totalCount % sheetDataRows % writeDataRows == 0 ? (totalCount / sheetDataRows / writeDataRows) : (totalCount / sheetDataRows / writeDataRows + 1));

                //开始分批查询分次写入
                //注意这次的循环就需要进行嵌套循环了,外层循环是Sheet数目,内层循环是写入次数
                List<Object> dataList = new ArrayList<>();
                response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName).getBytes("gb2312"), "ISO-8859-1") + ".xlsx");
                response.setContentType("multipart/form-data");
                response.setCharacterEncoding("utf-8");

                //<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<写入到多个sheet中《《《《《《《《《《《《《《《《《《《《《《《《《《《《--start
                     for (int i = 0; i < sheetNum; i++) {
                    //创建Sheet
                    Sheet sheet = new Sheet(i, 0);
                    sheet.setSheetName("sheel" + i);
                    //循环写入次数: j的自增条件是当不是最后一个Sheet的时候写入次数为正常的每个Sheet写入的次数,如果是最后一个就需要使用计算的次数lastSheetWriteCount
                    for (int j = 0; j < (i != sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j++) {
                        //集合复用,便于GC清理
                        dataList.clear();
                        //分页查询一次20w
                        int page = j + 1 + oneSheetWriteCount * i;
                        int pageSize = writeDataRows;
                        PageHelper.startPage(page, pageSize);
                        List<PsinfoAutomonirotDataAnomalSignExamine> reslultList = iSysNoticeService.selectPsInfoList();
                        if (!CollectionUtils.isEmpty(reslultList)) {
                            reslultList.forEach(item -> {
                                List<Object> list = new ArrayList<>();
                                Collections.addAll(list, item.getPsId(), item.getMpId(),
                                        item.getReviseFlag()==null?"": item.getReviseFlag(),
                                        item.getReviseFlagItem()==null?"":item.getReviseFlagItem());
                                dataList.add(list);
                            });
                        }
                        //写数据
                        writer.write(dataList, sheet, table);
                    }
                }
                //<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<写入到多个sheet中《《《《《《《《《《《《《《《《《《《《《《《《《《《《--end


                //分批次写入到一个sheeet 中-------------------------------start---

                //计算一个需要分批多少次
/*                int num = (totalCount/writeDataRows) +1;
                Sheet sheet = new Sheet(0);
                sheet.setSheetName("sheet");
                for(int i=0;i<num;i++){
                    //集合复用,便于GC清理
                    dataList.clear();
                    //分页查询一次20w
                    int page = i+1;
                    int pageSize = writeDataRows;
                    PageHelper.startPage(page, pageSize);
                    List<PsinfoAutomonirotDataAnomalSignExamine> reslultList = iSysNoticeService.selectPsInfoList();
                    if (!CollectionUtils.isEmpty(reslultList)) {
                        reslultList.forEach(item -> {
                            List<Object> list = new ArrayList<>();
                            Collections.addAll(list, item.getPsId(), item.getMpId(),
                                    item.getReviseFlag()==null?"": item.getReviseFlag(),
                                    item.getReviseFlagItem()==null?"":item.getReviseFlagItem());
                            dataList.add(list);
                        });
                    }
                    //写数据
                    writer.write(dataList, sheet, table);
                }*/


                //分批次写入到一个sheeet 中-------------------------------end---


                // 下载EXCEL
                writer.finish();
                outputStream.flush();
                //导出时间结束
                long endTime = System.currentTimeMillis();
                System.out.println("导出结束时间:" + endTime + "ms");
                System.out.println("导出所用时间:" + (endTime - startTime) / 1000 + "秒");
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                if (outputStream != null) {
                    try {
                        outputStream.close();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
        }
    }
}
